• Home
  • Courses Details

2500/-

   9239412412
Shib Shankar Ghosh
Developer

ADVANCED EXCEL

Advanced Excel Functions refers to the features and functions of Microsoft Excel, which helps the user to perform complex calculations, perform data analysis, and much more. In this article, you will learn some of the most commonly used advanced functions in Excel.

Overview

  • Classes 20
  • Duration 20 hours
  • Skill level Beginners to Advance
  • Mode Bengali/Hindi
  • Students 10-15
  • Assessments Yes
Course Description

This advanced Excel training course starts with a blank spreadsheet and quickly dives into using combinations of functions and formulas to perform dynamic analysis. The main formulas & functions covered in this training course include: INDEX and MATCH.

Certification

ISO MSME Certified Government Registered

Materials
  • Software Installation
  • Study Materials

SYLLABUS

  • 1. Excel 2007 & 2010, 2013, 2016 Quick Overview
    • Lesson 1. Difference between Excel 2003, 2007 and 2010,2013,2016.
    • Lesson 2. Use of Excel, its boundaries & features.
  • 2. Basic Formula
    • Lesson 1. Formulae that Add / Subtract / Multiply / Divide.
    • Lesson 2. BODMAS / Formula Error Checking.
    • Lesson 3. The Sum Function.
  • 3. Absolute Referencing
    • Lesson 1. Problems with Absolute / Relative Cell Referencing.
    • Lesson 2. Creating Absolute / Mixed References.
  • 4. LOOKUP Function
    • Lesson 1. The VLOOKUP/ HLOOKUP Functions.
    • 5. PivotTables
    • Lesson 1. Creating, Formatting Simple PivotTables.
    • Lesson 2. Page Field in a PivotTable.
    • Lesson 3. Formatting a PivotTable.
    • Lesson 4. Creating / Modifying a PivotChart.
  • 6. Logical Functions
    • Lesson 1. IFs and Nested IF functions.
    • Lesson 2. Using AND / OR / NOT Functions.
  • 7. Statistical Functions
    • Lesson 1. Using The SUMIF / COUNTIF Functions.
    • Lesson 2. Using The AVERAGE / COUNT / LARGER / SMALLER Functions.
  • 8. Pivot Tables – Advance
    • Lesson 1. Adding new calculated Fields / Items.
    • Lesson 2. Changing the Summary Function.
    • Lesson 3. Consolidate Pivot table.
  • 9. LOOKUP Functions – Advance
    • Lesson 1. MATCH with VLOOKUP Functions.
    • Lesson 2. INDEX & MATCH Functions.
    • Lesson 3. OFFSET / INDIRECT functions.
  • 10. Logical Functions – Advance
    • Lesson 1. If Loop and Nested IF Loop Functions.
    • Lesson 2. Using IF / ISERROR function.
  • 11. Chart Data Techniques
    • Lesson 1. The Chart Wizard.
    • Lesson 2. Chart Types.
    • Lesson 3. Adding Title / Legends / Lables.
    • Lesson 4. Printing Charts.
    • Lesson 5. Adding Data to a Chart.
    • Lesson 6. Formatting / Renaming / Deleting Data Series.
    • Lesson 7. Changing the Order of Data Series.
  • 12. Date / Time Functions
    • Lesson 1. Using The Today.
    • Lesson 2. Now & Date Functions.
    • Lesson 3. Using The Datedif / Networkdays / Eomonth Functions.
    • Lesson 4. Using The Weeknum Functions.
    • Lesson 5. Using The Edate / Networkdays. Intl / Weekdays.Intl functions.
  • 13. Text Functions Using
    • Lesson 1. The Mid / Search / Left / Right Functions.
    • Lesson 2. Using The Trim / Clean / Upper/ Lower function.
    • Lesson 3. Using The Substitute / Text Functions.
    • Lesson 4. Using The Trim / Clean / Proper / Dollar Function.
  • 14. Validations
    • Lesson 1. Input Messages / Error Alerts / Drop-Down.
    • Lesson 2. Conditional Formatting.
  • 15. Advanced Filters
    • Lesson 1. Extracting Records with Advanced Filter.
    • Lesson 2. Using Formulas in Criteria.
  • 16. Advanced Sorting
    • Lesson 1. Sorting by Top to Bottom / Left to Right.
    • Lesson 2. Creating / Deleting Custom List.
    • Lesson 3. Sort by using Custom List.
  • 17. Hyper / Data Linking
    • Lesson 1. Hyper linking data, within sheet / workbook.
    • Lesson 2. Linking & Updating links between workbooks & application.
  • 18. Math & Trigonometry Functions
    • Lesson 1. Using SUMPRODUCT Functions.
    • Lesson 2. Using FLOOR / CEILING/ MROUND / MOD / QUOTIENT Functions.
  • 19. Summarizing Data
    • Lesson 1. Creating Subtotals / Nested Subtotals.
    • Lesson 2. SUBTOTALS Formula.
  • 20. Outlining
    • Lesson 1. Creating / Working with an Automatic / Manual.
    • Lesson 2. Grouping / Ungrouping.
  • 21. Consolidation
    • Lesson 1. Consolidating Data with Identical / Different Layout.
  • 22. Using Auditing Tools
    • Lesson 1. Displaying / Removing Dependent & Precedent Arrows.
    • Lesson 2. Evaluate Formula – Step IN / Step Out.
  • 23. Custom Views
    • Lesson 1. Creating Custom Views.
    • Lesson 2. Displaying Custom Views.
    • Lesson 3. Deleting Custom Views.
  • 24. Sharing and Protecting Workbooks
    • Lesson 1. Sharing Workbooks & Tracking Changes.
    • Lesson 2. Protecting sheets / workbooks / Files.
  • 25. Importing & Exporting Data
    • Lesson 1. Importing Data from Database / Text Files / Web.
    • Lesson 2. Exporting Data.
    • Lesson 3. Changing External Data Range.